from functools import wraps

import pymysql


class ConnDb:

    def __init__(self, host, user, password, db):
        self.cursor = None
        self.host = host
        self.user = user
        self.password = password
        self.db = db

    # 装饰器:打开连接，执行sql，关闭连接
    def open_close_db(func):
        @wraps(func)
        def run(self, sql=None):
            # 创建数据库连接
            print('创建数据库连接')
            cc = pymysql.connect(host=self.host, port=3306, user=self.user, password=self.password,
                                 db=self.db, charset='utf8')
            # 创建游标
            print('创建游标')
            self.cursor = cc.cursor()
            try:
                # 运行sql语句, 得到返回值
                print(f'运行sql语句:{sql}')
                li = func(self, sql)
                # 提交事务
                # print('提交事务')
                # cc.commit()
            except Exception as e:
                # 如果出现错误，回滚事务
                cc.rollback()
                # 打印报错信息
                print('运行', str(func), '方法时出现错误，错误代码：', e)
            finally:
                # 关闭游标和数据库连接
                print('关闭游标和数据库连接')
                self.cursor.close()
                cc.close()
            try:
                # 返回sql执行信息
                print(f'返回结果:{li}')
                return li
            except:
                print('没有得到返回值，请检查代码，该信息出现在ConnDb类中的装饰器方法open_close')

        return run

    def __get_index_dict(self):
        """ConnDb
        获取数据库对应表中的字段名
        """
        index_dict = dict()
        index = 0
        for desc in self.cursor.description:
            index_dict[desc[0]] = index
            index = index + 1
        # print(index_dict)
        return index_dict

    @open_close_db
    def get_dict_data_sql(self, sql):
        """
        运行sql语句，获取结果，并根据表中字段名，转化成dict格式（默认是tuple格式）
        """
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        index_dict = self.__get_index_dict()
        res = []
        for datai in data:
            resi = dict()
            for indexi in index_dict:
                resi[indexi] = datai[index_dict[indexi]]
            res.append(resi)
        return res


if __name__ == '__main__':
    pass